package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//此类对菜品的数据库进行操作
//1.增加菜品
//2.删除菜品
//3.查询所有菜品
//4.查询指定菜品名
//5.修改指定菜品名及价格
   public class DishesDao {
       public void add(Dishes dish) throws OrderSystemExecption {
           //1.建立连接
           Connection connection = DBUtil.getConnection();
           //2.构造 sql 语句
           String sql = " insert into dishes values(null,?,?)";
           PreparedStatement statement = null;
           try {
                statement = connection.prepareStatement(sql);
                statement.setString(1, dish.getName());
                statement.setInt(2,dish.getPrice());
                //3执行 sql
                int ret = statement.executeUpdate();
               if (ret != 1) {
                   throw new OrderSystemExecption("插入菜品失败");
               }
               System.out.println("插入用户成功");
           } catch (SQLException | OrderSystemExecption throwables) {
               throwables.printStackTrace();
               throw new OrderSystemExecption("插入菜品失败");
           }finally {
               DBUtil.close(connection,statement,null);
           }
       }
       public void deleteByName(String name) throws Exception {
           //1.连接数据库
           Connection connection = DBUtil.getConnection();
           //2.构造 sql
           String sql = "delete from dishes where name = ?";
           PreparedStatement statement = null;
           try {
               statement = connection.prepareStatement(sql);
               statement.setString(1,name);
               //3.执行 sql
             int ret = statement.executeUpdate();
             if (ret!=1){
                 throw new Exception("删除菜品失败");
             }
               System.out.println("删除菜品成功");
           } catch (SQLException throwables) {
               throwables.printStackTrace();
               throw new Exception("删除菜品失败");
           } catch (Exception e) {
               e.printStackTrace();
               //4.关闭资源
           }finally {
               DBUtil.close(connection,statement,null);
           }
       }
       public List<Dishes> selectByAll() throws Exception {
           List<Dishes> dishes = new ArrayList<>();
           Connection connection = DBUtil.getConnection();
           String sql = "select * from dishes";
           PreparedStatement statement = null;
           ResultSet resultSet = null;
           try {
               statement = connection.prepareStatement(sql);

              resultSet = statement.executeQuery();
             while (resultSet.next()){
                 Dishes dish = new Dishes();
                 dish.setDishid(resultSet.getInt("dishid"));
                 dish.setName(resultSet.getString("name"));
                 dish.setPrice(resultSet.getInt("price"));
                 dishes.add(dish);
             }

           } catch (SQLException throwables) {
               throwables.printStackTrace();
               throw new Exception("查看所有菜品失败");
           }finally {
               DBUtil.close(connection,statement,resultSet);
           }
           return dishes;
       }
       public Dishes selectByDishiId(int dishid) throws OrderSystemExecption {
           Connection connection = DBUtil.getConnection();
           String sql = "select * from dishes where dishid = ?";
           PreparedStatement statement = null;
           ResultSet resultSet = null;
           try {
               statement = connection.prepareStatement(sql);
               statement.setInt(1,dishid);
               resultSet = statement.executeQuery();
               if(resultSet.next()){
                   Dishes dish = new Dishes();
                   dish.setDishid(resultSet.getInt("dishid"));
                   dish.setName(resultSet.getString("name"));
                   dish.setPrice(resultSet.getInt("price"));
                   return dish;
               }

           } catch (SQLException throwables) {
               throwables.printStackTrace();
               throw new OrderSystemExecption("根据菜单 id 查询菜单名称失败");
           }finally {
               DBUtil.close(connection,statement,resultSet);
           }
           return null;

       }

    public static void main(String[] args) throws Exception {
        DishesDao dishesDao = new DishesDao();
        Dishes dish = new Dishes();
        dish.setName("红烧茄子");
        dish.setDishid(2);
        dish.setPrice(20);
        dishesDao.add(dish);
//        List<Dishes> dishes = dishesDao.selectByAll();
//        // 查询所有菜单
//        System.out.println(dishes);
//        Dishes dishes1 = dishesDao.selectByDishiId(6);
//        // 查询指定 id 菜名
//        System.out.println(dishes1);
//        dishesDao.deleteByName("红烧茄子");
    }
}
